local lfs = reload("api.lfs")
local class = reload("class")
local super = reload("super.basic")
local t2string = reload("t2string")
local logdebug = reload("single.logdebug")
local gsplit = reload("string.gsplit")
local ifCompare = reload("ifCompare")

---@class api_mysqldump:unknown @mysql转储
local this = class(super)

---构造函数
function this:ctor()
end

---@param mdb mysqlLC              @数据库连接
---@param syn boolean              @是否同步
---@param dname string             @数据库名字-内网
function this:launch(mdb, syn, dbname)
    if syn then
        self:async(self.mysql2mdb, {}, mdb, dbname)
    else
        self:async(self.mysql2cfg, {}, mdb, dbname)
    end
end

---读取mysql当前结构
function this:readMysqlStruct(carg, mdb)
    local tables = {}
    local column = {}
    local indexs = {}
    local proces = {}
    local functs = {}

    ---读取表列表
    mdb:showTableList()(self.coback, self, carg)
    local tbs = self:await()

    ---读取创建表语句
    for i, tbname in ipairs(tbs) do
        ---读取数据表创建语句
        mdb:showCreateTable(tbname)(self.coback, self, carg)
        tables[tbname] = self:await()
        ---读取数据表字段信息
        mdb:showTableColumn(tbname)(self.coback, self, carg)
        column[tbname] = self:await()
        ---读取数据表索引信息
        mdb:showTableIndex(tbname)(self.coback, self, carg)
        indexs[tbname] = self:await()
    end

    ---读取存储列表
    mdb:showProcedureList()(self.coback, self, carg)
    local pcs = self:await()
    ---存储过程创建
    for _, pcname in ipairs(pcs) do
        ---读取存储过程
        mdb:showCreateProcedure(pcname)(self.coback, self, carg)
        proces[pcname] = self:await()
    end

    ---读取函数列表
    mdb:showFunctionList()(self.coback, self, carg)
    local fcs = self:await()
    ---函数创建语句
    for _, fcname in ipairs(fcs) do
        ---读取存储过程
        mdb:showCreateFunction(fcname)(self.coback, self, carg)
        functs[fcname] = self:await()
    end
    return {
        tables = tables, ---表创建语句
        column = column, ---表字段信息
        indexs = indexs, ---表索引信息
        proces = proces, ---存储创建语句
        functs = functs, ---函数创建语句
    }
end

---读取本地文件的结构
function this:readLocalStruct(dbname)
    local root = "./dbs"
    local dbdir = string.format("%s/%s", root, dbname)

    ---@type table<string,string>
    local tmap = {
        ["table"] = "tables",
        ["index"] = "indexs",
        ["column"] = "column",
        ["function"] = "functs",
        ["procedure"] = "proces",
    }

    --遍历目录下的lua文件
    local patchs = lfs.recursiveFiles(dbdir .. "/", {}, ".lua")

    local ret = {
        tables = {},
        proces = {},
        functs = {},
        indexs = {},
        column = {},
    }

    for i, path in ipairs(patchs) do
        local gsp = gsplit(path, "/")

        local dbname = gsp[3]
        local tpname = gsp[4]
        local finame = gsp[5]
        repeat
            if not finame then
                break
            end

            local mode = finame:sub(1, -5)
            local load = string.format("%s.%s.%s", dbname, tpname, mode)
            tpname = tmap[tpname]
            if tpname then
                local data = require(load)
                _G.package.loaded[load] = nil

                ret[tpname][mode] = data
            end
        until true
    end

    return ret
end

function this:writeFileStruct(carg, mdb, dbname, struct)
    ---创建根目录结构
    local root = "./dbs"
    lfs.mkdir(root)

    ---创建数据库目录
    local dbdir = string.format("%s/%s", root, dbname)
    ---删除数据库目录
    local rmrf = string.format("rm -rf %s", dbdir)
    os.execute(rmrf)
    lfs.mkdir(dbdir)

    ---保存数据表结构
    local tbdir = dbdir .. "/table"
    local fcdir = dbdir .. "/function"
    local pcdir = dbdir .. "/procedure"
    local indir = dbdir .. "/index"
    local cmdir = dbdir .. "/column"
    local emdir = dbdir .. "/emmlua"
    lfs.mkdir(dbdir)
    lfs.mkdir(tbdir)
    lfs.mkdir(fcdir)
    lfs.mkdir(pcdir)
    lfs.mkdir(indir)
    lfs.mkdir(cmdir)
    lfs.mkdir(emdir)

    ---数据表创建语句
    for tbname, def in pairs(struct.tables) do
        local file = io.open(tbdir .. "/" .. tbname .. ".lua", "w")
        file:write(t2string(def, "return [[", "]]\n"))
        file:close()
    end

    ---存储过程创建语句
    for pcname, def in pairs(struct.proces) do
        local file = io.open(pcdir .. "/" .. pcname .. ".lua", "w")
        file:write(t2string(def, "return [[", "]]\n"))
        file:close()
    end

    ---函数方法创建语句
    for fcname, def in pairs(struct.functs) do
        local file = io.open(fcdir .. "/" .. fcname .. ".lua", "w")
        file:write(t2string(def, "return [[", "]]\n"))
        file:close()
    end

    ---数据表索引信息
    for idname, def in pairs(struct.indexs) do
        local file = io.open(indir .. "/" .. idname .. ".lua", "w")
        file:write(t2string(def, "return ", "\n"))
        file:close()
    end

    ---数据表字段信息
    for tbname, def in pairs(struct.column) do
        local file = io.open(cmdir .. "/" .. tbname .. ".lua", "w")
        file:write(t2string(def, "return ", "\n"))
        file:close()
    end

    ---读取数据表注解
    mdb:selectTableComment()(self.coback, self, carg)
    local mapmment = self:await()

    ---table /emmlua
    local emmlua = {}

    for tbname, def in pairs(struct.column) do
        table.insert(emmlua, "---@class ")
        table.insert(emmlua, tbname)
        table.insert(emmlua, " @")
        table.insert(emmlua, mapmment[tbname] or tbname)
        table.insert(emmlua, "\n")

        for field, v in pairs(def) do
            table.insert(emmlua, "---@field ")
            table.insert(emmlua, field)
            table.insert(emmlua, " ")
            table.insert(emmlua, gsplit(v.Type, "%b()")[1])
            table.insert(emmlua, " @")
            table.insert(emmlua, v.Comment)
            table.insert(emmlua, " ")
            table.insert(emmlua, "\n")
        end

        table.insert(emmlua, "\n")
    end

    local file = io.open(emdir .. "/" .. dbname .. ".lua", "w")
    file:write(table.concat(emmlua))
    file:close()
end

---拉取mysql结构保存本地
function this:mysql2cfg(carg, mdb, dbname)
    logdebug("读取mysql结构开始")
    local struct = self:readMysqlStruct(carg, mdb)
    self:writeFileStruct(carg, mdb, dbname, struct)
    logdebug("读取mysql结构成功,并且写入本地结束")
end

---本地mysql结构同步入库
function this:mysql2mdb(carg, mdb, dbname)
    local structLocal = self:readLocalStruct(dbname)
    local structMysql = self:readMysqlStruct(carg, mdb)
    if ifCompare(structLocal, structMysql) then
        logdebug("远程和本地结构一致，无需同步")
        return
    else
        logdebug("远程和本地结构不同，开始同步")
    end

    ---删除远程数据表
    for tbname, def in pairs(structMysql.tables) do
        if not structLocal.tables[tbname] then
            mdb:dropTable(tbname)(self.coback, self, carg)
            logdebug("删除远程数据表:", tbname)
        end
    end

    ---添加远程数据表
    for tbname, sql in pairs(structLocal.tables) do
        if not structMysql.tables[tbname] then
            mdb:create(sql)
            logdebug("添加远程数据表:", tbname)
        end
    end

    ---修改远程数据表(比较数据列的不同)->添加/修改字段
    for tbname, aTable in pairs(structLocal.column) do
        repeat
            local bTable = structMysql.column[tbname]
            if not bTable then
                break ---数据表不存在
            end

            if ifCompare(aTable, bTable) then
                break ---数据表一致
            end

            ---添加+修改字段
            for field, aInfo in pairs(aTable) do
                local bInfo = bTable[field]
                if not bInfo then
                    ---添加字段
                    local sql = self:addfield(tbname, aInfo, bInfo)
                    mdb:alter(sql)
                    logdebug("添加远程数据表字段:", tbname, field)
                elseif not ifCompare(aInfo, bInfo) then
                    ---修改字段
                    local sql = self:chgfield(tbname, aInfo, bInfo)
                    mdb:alter(sql)
                    logdebug("修改远程数据表字段:", tbname, field)
                end
            end
        until true
    end

    ---修改远程数据表(比较数据列的不同)->删除字段
    for tbname, aTable in pairs(structMysql.column) do
        local bTable = structLocal.column[tbname]
        if not bTable then
            break ---数据表不存在
        end

        if ifCompare(aTable, bTable) then
            break ---数据表一致
        end

        ---删除字段
        for field, aInfo in pairs(aTable) do
            local bInfo = bTable[field]
            if not bInfo then
                local sql = self:dropfield(tbname, aInfo)
                mdb:alter(sql)
                logdebug("删除远程数据表字段:", tbname, field)
            end
        end
    end

    ---添加远程数据表索引--添加索引-修改索引
    for tbname, aIndexs in pairs(structLocal.indexs) do
        repeat
            local bIndexs = structMysql.indexs[tbname]
            if not bIndexs then
                break ---新创建表
            end

            if ifCompare(aIndexs, bIndexs) then
                break ---索引一致
            end

            for aName, aInfo in pairs(aIndexs) do
                local bInfo = bIndexs[aName]
                if not bInfo then
                    local sql = self:addIndex(aInfo, structLocal)
                    mdb:alter(sql)
                    logdebug("添加远程数据表索引:", tbname, aName)
                elseif not ifCompare(aInfo, bInfo) then
                    local sql1, sql2 = self:chgIndex(aInfo, bInfo, structLocal)
                    mdb:alter(sql1)
                    mdb:alter(sql2)
                    logdebug("修改远程数据表索引:", tbname, aName)
                end
            end
        until true
    end

    ---删除远程数据表索引---索引删除
    for tbname, aIndexs in pairs(structMysql.indexs) do
        repeat
            local bIndexs = structLocal.indexs[tbname]
            if not bIndexs then
                break ---删除了的表
            end

            if ifCompare(aIndexs, bIndexs) then
                break ---索引一致
            end

            for aName, aInfo in pairs(aIndexs) do
                local bInfo = bIndexs[aName]
                if not bInfo then
                    local sql = self:delIndex(aInfo)
                    mdb:alter(sql)
                    logdebug("添加远程数据表索引:", tbname, aName)
                end
            end
        until true
    end

    ---删除远程存储过程
    for name, sql1 in pairs(structMysql.proces) do
        local sql2 = structLocal.proces[name]
        if not sql2 then
            mdb:dropProcedure(name)
            logdebug("删除远程存储过程:", name)
        elseif sql1 ~= sql2 then
            mdb:dropProcedure(name)
            logdebug("删除远程存储过程:", name)
        end
    end

    ---创建远程存储过程
    for name, sql1 in pairs(structLocal.proces) do
        local sql2 = structMysql.proces[name]
        if (not sql2) or (sql1 ~= sql2) then
            mdb:create(sql1)
        end
    end

    ---删除远程函数
    for name, sql1 in pairs(structMysql.functs) do
        local sql2 = structLocal.functs[name]
        if not sql2 then
            mdb:dropFunction(name)
            logdebug("删除远程函数:", name)
        elseif sql1 ~= sql2 then
            mdb:dropFunction(name)
            logdebug("删除远程函数:", name)
        end
    end

    ---创建远程函数
    for name, sql1 in pairs(structLocal.functs) do
        local sql2 = structMysql.functs[name]
        if (not sql2) or (sql1 ~= sql2) then
            mdb:create(sql1)
        end
    end

    logdebug("远程和本地结构不同，同步完成")
end

---修改字段
---@param tbname string @数据表
---@param n_cmn table_columns @新字段
---@param o_cmn table_columns @旧字段
function this:chgfield(tbname, n_cmn, o_cmn)
    local null = ""
    if n_cmn.Null == "NO" then
        null = "NOT NULL"
    end

    local default = ""

    if not n_cmn.Default then
        if n_cmn.Null ~= "NO" then
            default = "DEFAULT NULL"
        end
    elseif n_cmn.Default == "CURRENT_TIMESTAMP" then
        default = string.format("DEFAULT %s", n_cmn.Default)
    else
        default = string.format("DEFAULT '%s'", n_cmn.Default)
    end

    local collate = ""

    if n_cmn.Collation then
        collate = string.format("COLLATE '%s'", n_cmn.Collation)
    end

    local cmt = ""

    if n_cmn.Comment then
        cmt = string.format("COMMENT '%s'", n_cmn.Comment)
    end

    local old_field = o_cmn and o_cmn.Field or n_cmn.Field
    local fmt = "ALTER TABLE `%s` CHANGE COLUMN `%s` `%s` %s %s %s %s %s %s"
    local sql = string.format(fmt,
        tbname,
        old_field,
        n_cmn.Field,
        n_cmn.Type,
        null,
        default or '',
        cmt,
        collate,
        n_cmn.Extra)
    return sql
end

---增加字段
---@param tbname string @数据表
---@param n_cmn table_columns @新字段
---@param o_cmn table_columns @旧字段
function this:addfield(tbname, n_cmn, o_cmn)
    local chgsql = this:chgfield(tbname, n_cmn, o_cmn)
    local addsql = string.gsub(chgsql, "CHANGE", "ADD")
    return addsql
end

---删除字段
---@param tbname string @数据表
---@param n_cmn table_columns @新字段
function this:dropfield(tbname, n_cmn)
    local fmt = "ALTER TABLE `%s` DROP COLUMN `%s`"
    local sql = string.format(fmt, tbname, n_cmn.Field)
    return sql
end

---添加索引
---@param info table_indes @索引信息
---@param struct table @表结构
function this:addIndex(info, struct)
    -- 转义列名（处理MySQL关键字）
    local escapedColumns = {}
    for _, colName in ipairs(info.Column_name) do
        table.insert(escapedColumns, "`" .. colName .. "`")
    end
    local columnsStr = table.concat(escapedColumns, ",")

    if info.Key_name == 'PRIMARY' then
        ---主键类型
        local auto = ""
        local names = info.Column_name
        for i, v in ipairs(names) do
            local colum = struct.column[info.Table][v]
            if colum.Extra == "auto_increment" then
                auto = string.format(
                    "ALTER TABLE `%s` MODIFY COLUMN `%s` %s AUTO_INCREMENT;",
                    info.Table, v, colum.Type)
                break
            end
        end

        return string.format("ALTER TABLE `%s` ADD PRIMARY KEY(%s); %s",
            info.Table, columnsStr, auto)
    elseif info.Non_unique == 0 then
        -- 修复：USING BTREE 放在列定义后面，或者省略
        return string.format("ALTER TABLE `%s` ADD UNIQUE INDEX `%s`(%s) USING %s COMMENT '%s';",
            info.Table, info.Key_name, columnsStr, info.Index_type, info.Index_comment or "")
    else
        return string.format("ALTER TABLE `%s` ADD INDEX `%s`(%s) COMMENT '%s';",
            info.Table, info.Key_name, columnsStr, info.Index_comment or "")
    end
end

---删除索引
---@param info table @索引信息
function this:delIndex(info)
    return string.format("ALTER TABLE `%s` DROP INDEX `%s`;", info.Table, info.Key_name)
end

---修改索引
---@param aInfo table_indes @新索引
---@param bInfo table_indes @旧索引
---@param struct table @表结构
function this:chgIndex(aInfo, bInfo, struct)
    local sql1 = self:delIndex(bInfo)
    local sql2 = self:addIndex(aInfo, struct)
    return sql1, sql2
end

return this

---@class table_columns @表字段属性
---@field Comment       string @字段注释,                                           1
---@field Null          string @是否为空[NO|YES]                                    1
---@field Key           string @是否主键["PRI"|""]                                  1
---@field Type          string @字段类型["int unsigned"]                            1
---@field Privileges    string @字段权限["select,insert,update,references"]         0
---@field Field         string @字段名字                                            1
---@field Extra         string @unknown[”auto_increment“|""]                       1
---@field Default       string @默认值                                              1
---@field Collation     string @编码排序                                            1

---@class table_indes @表索引属性
---@field Index_type    string @索引类型["BTREE"]
---@field Cardinality   number @索引基数[4099]
---@field Non_unique    number @是否重复[0|1]
---@field Collation     string @升降排序["A"|"NULL"]
---@field Commen        string @多种评注
---@field Seq_in_index  number @列序列号[1]
---@field Table         string @数据表名
---@field Key_name      string @索引名字["PRIMARY"]
---@field Null          string @[""]
---@field Index_comment string @索引注释
---@field Visible       string @["YES"]
---@field Column_name   string[] @谁在使用
